package com.csqf.booksys.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.csqf.booksys.dao.IBookDao;
import com.csqf.booksys.entity.Book;
import com.csqf.booksys.entity.PageBean;
import com.csqf.booksys.utils.DbUtils;

/**
 * @author enbo.xie
 * @Date 2020年8月12日 下午4:23:28
 */
public class BookDaoImpl implements IBookDao{
    QueryRunner qr = new QueryRunner(DbUtils.getDataSource());
    
	@Override
	public int insert(Book book) {
		int flag = -1;
		try {
			String sql = " INSERT INTO book VALUES(NULL,?,?,?,?,?) ";
			Object [] arr = {book.getName(),book.getAuthor(),book.getPublish(),book.getPrice(),book.getCrtTime()};
			flag = qr.update(sql, arr);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public int delete(int bid) {
		int flag = -1;
		try {
			String sql = " delete from book where bid=? ";
			flag = qr.update(sql, bid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	@Override
	public int update(Book book) {
		int flag = -1;
		try {
			String sql = " UPDATE book SET name=?,author=?,publish=?,price=?,crtTime=? WHERE bid=? ";
			Object [] arr = {book.getName(),book.getAuthor(),book.getPublish(),book.getPrice(),book.getCrtTime(),book.getBid()};
			flag = qr.update(sql, arr);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	
	@Override
	public Book select(int bid) {
		Book book = null;
		try {
			String sql = " select * from book where bid=? ";
			book = qr.query(sql, new BeanHandler<Book>(Book.class), bid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return book;
	}

	@Override
	public List<Book> select(String name) {
		List<Book> list = null;
		try {
			//String sql = " SELECT * FROM book WHERE NAME LIKE ？ ";
			//list = qr.query(sql, new BeanListHandler<Book>(Book.class),"%"+name+"%");
			String sql = " SELECT * FROM book WHERE NAME LIKE concat('%',?,'%') ";
			list = qr.query(sql, new BeanListHandler<Book>(Book.class),name);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Book> select() {
		List<Book> list = null;
		try {
			String sql = " SELECT * FROM book ";
			list = qr.query(sql, new BeanListHandler<Book>(Book.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public void selectPage(PageBean<Book> pb) {
		//1、获取当前的总记录数
		int count = (int)selectAll();
		pb.setTotalNum(count);
		//判断当前页数是否符合
		if(pb.getCurrentPage() <= 1){
			pb.setCurrentPage(1);
		}else if(pb.getCurrentPage() >= pb.getTotalPage()){
			pb.setCurrentPage(pb.getTotalPage());
		}
		//组装数据
		int index = (pb.getCurrentPage()-1)*pb.getPageSize();
		Object [] param = {index,pb.getPageSize()};
		String pageSql = " SELECT * FROM book LIMIT ?,? ";
		try {
			List<Book> list = qr.query(pageSql,new BeanListHandler<Book>(Book.class),param);
			pb.setDataList(list);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public long selectAll() {
		long count = 0L;
		String sql = " select count(*) from book ";
		try {
			count = qr.query(sql, new ScalarHandler<>());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

}
